using System;
using System.Collections.Generic;
using Com.AlbLab.GEP.Domain;
using Com.AlbLab.GEP.Domain.Entities;
using Com.AlbLab.GEP.Dao.Interface;
using Com.AlbLab.GEP.Dao.Base;
using Oracle.DataAccess.Client;
using System.Data;

namespace Com.AlbLab.GEP.Dao.Impl
{
    public class UtenteDao : OracleDao<OracleSessionManager, ISessionModel>, IUtenteDao
    {

        public UtenteDao(OracleSessionManager daoContext) : base(daoContext)
        {
            if (!base.Context.Session_isActive())
            {
                base.Context.Session_Init();
                base.CurrentConnection.Open();
            }
        }
        private string seletctAll = "select * from GEP_UTENTI";
        private string selectById = "select * from GEP_UTENTI WHERE ID=:pID";
        private string update = "update GEP_UTENTI SET USERNAME=:pUSERNAME,PASSWORD=:pPASSWORD,NOME=:pNOME,COGNOME=:pCOGNOME,ISADMIN=:pISADMIN,ROLEC=:pROLEC,ROLEV=:pROLEV WHERE ID=:pID";
        private string delete = "delete GEP_UTENTI WHERE ID=:pID";
        private string insert = "insert into GEP_UTENTI (ID,USERNAME,PASSWORD,NOME,COGNOME,ISADMIN,ROLEC,ROLEV) VALUES(ASM_GEP_USER_ID.NEXTVAL,:pUSERNAME,:pPASSWORD,:pNOME,:pCOGNOME,:pISADMIN,:pROLEC,:pROLEV) RETURNING ID INTO :pID";

        private string selectByUserName = "select * from GEP_UTENTI WHERE USERNAME=:pUSERNAME";
        private string updatePassword = "update GEP_UTENTI SET PASSWORD=:pPASSWORD WHERE ID=:pID";
       
       
        public UtenteEntity findByUserName(string Username)
        {
            Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(selectByUserName,CurrentConnection);
            cmd.Parameters.Add("pUSERNAME", Username);
            UtenteEntity ut=null;
            try
            {
                using (OracleDataReader r = cmd.ExecuteReader())
                {
                    if (r.HasRows)
                    {
                        r.Read();
                        ut = DaoOracleDbHelper.MapUtente(r);
                    }
                }
            }
            catch (Exception e0x)
            {
                throw new ManagedException("EX-UT001", e0x);
            }
            return ut;
        }

        public Dictionary<decimal, UtenteEntity> GetAll()
        {
            Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(seletctAll, CurrentConnection);
            Dictionary<decimal, UtenteEntity> list = null;
            try
            {
                using (OracleDataReader r = cmd.ExecuteReader())
                {
                    if (r.HasRows)
                    {
                        list = new Dictionary<decimal, UtenteEntity>();
                        while (r.Read())
                        {
                            UtenteEntity t = DaoOracleDbHelper.MapUtente(r);
                            list.Add(t.Id,t);
                        }
                    }
                }
            }
            catch (Exception e0x)
            {
                throw new ManagedException("EX-UT003", e0x);
            }
            return list;
        }

        public UtenteEntity GetById(decimal id)
        {
            Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand(selectById, CurrentConnection);
            cmd.Parameters.Add("pID", id);
            UtenteEntity ut = null;
            try
            {
                using (OracleDataReader r = cmd.ExecuteReader())
                {
                    if (r.HasRows)
                        r.Read();
                    ut = DaoOracleDbHelper.MapUtente(r);
                }
            }
            catch (Exception e0x)
            {
                throw new ManagedException("EX-UT002", e0x);
            }
            return ut;
        }

        public void Insert(UtenteEntity entity)
        {
            using (OracleCommand ocmd = new OracleCommand())
            {
                try
                {
                    ocmd.CommandText = insert;
                    ocmd.Parameters.AddRange(MappingParams(entity, false));
                    ocmd.Connection = base.CurrentConnection;
                    ocmd.ExecuteNonQuery();
                    entity.Id = (decimal)ocmd.Parameters["pID"].Value;
                }
                catch (Exception e0x)
                {
                    throw new ManagedException("EX-UT004", e0x);
                }
            }
        }

        public void Update(UtenteEntity entity)
        {
            using (OracleCommand ocmd = new OracleCommand())
            {
                try
                {
                    ocmd.CommandText = update;
                    ocmd.Parameters.AddRange(MappingParams(entity, false));
                    ocmd.Connection = base.CurrentConnection;
                    ocmd.ExecuteNonQuery();
                }
                catch (Exception e0x)
                {
                    throw new ManagedException("EX-UT005", e0x);
                }
            }
            
        }

        public void Delete(decimal id)
        {
            using (OracleCommand ocmd = new OracleCommand())
            {
                try
                {
                    ocmd.CommandText = delete;
                    ocmd.Parameters.Add("pID", id);
                    ocmd.Connection = base.CurrentConnection;
                    if (ocmd.ExecuteNonQuery() != 1)
                        throw new ManagedException("Nessuna Riga Modificata", "EX-UT006", "", "", "", "", "", null);
                }
                catch (Exception e0x)
                {
                    if (e0x.GetType().Equals(typeof(ManagedException))) throw;
                    throw new ManagedException("EX-UT007", e0x);
                }
            }

        }

        public void Dispose()
        {
            if (!base.Context.TransactionModeOn)
            {
                base.CurrentConnection.Close();
            }
        }

        private OracleParameter[] MappingParams(UtenteEntity r, bool isInsert)
        {
            OracleParameter[] oparams;
            if (isInsert)
                oparams = new OracleParameter[9];
            else oparams = new OracleParameter[8];
            
            oparams[0] = new OracleParameter("pUSERNAME", OracleDbType.NVarchar2, r.Username, ParameterDirection.Input);
            oparams[1] = new OracleParameter("pPASSWORD", OracleDbType.NVarchar2, r.Password, ParameterDirection.Input);
            oparams[2] = new OracleParameter("pNOME", OracleDbType.NVarchar2, r.Nome, ParameterDirection.Input);
            oparams[3] = new OracleParameter("pCOGNOME", OracleDbType.NVarchar2, r.Cognome, ParameterDirection.Input);
            oparams[4] = new OracleParameter("pISADMIN", OracleDbType.Int32, Convert.ToInt32(r.IsAdmin), ParameterDirection.Input);
            oparams[5] = new OracleParameter("pROLEC", OracleDbType.Int32, Convert.ToInt32(r.RoleC), ParameterDirection.Input);
            oparams[6] = new OracleParameter("pROLEV", OracleDbType.Int32, Convert.ToInt64(r.RoleV), ParameterDirection.Input);
            if (isInsert)
            {
                oparams[7] = new OracleParameter("pID", OracleDbType.Decimal, r.Id, ParameterDirection.Output);
            }
            else
                oparams[7] = new OracleParameter("pID", OracleDbType.Decimal, r.Id, ParameterDirection.Input);

            return oparams;
        }

    }
}
